package presenters;

import java.sql.ResultSet;
import java.sql.SQLException;

import model.ConnectionManager;
import model.IPresenter;
import views.MaintHistoryReportPanel;

public class MaintHistoryReportPresenter extends IPresenter {
	private MaintHistoryReportPanel myPanel;

	public MaintHistoryReportPresenter(
			MaintHistoryReportPanel panel) {
		super();
		myPanel = panel;
	}
	
	public void generateReport() {
		System.out.println("Generate Maintaince History Report");
		
		String sqlDrop = "DROP VIEW " + ConnectionManager.GROUP + ".NUM_PROBS;";
		int out = executeSQLUpdate(sqlDrop);
		System.out.println("Drop out is: " + out);
		
		String sqlView = ""
		+ "CREATE VIEW NUM_PROBS AS SELECT DISTINCT "
		+ "RQST_VIN, "
		+ "COUNT(PROBLEM_DISC) AS NUM_PROBS "
		+ "FROM SERVICE_REQUEST_PROBLEM GROUP BY RQST_VIN; ";

		String sqlSelect = "" 
		+ "SELECT "
			+ "B.VIN, "
			+ "B.MDL_NM AS Car, "
			+ "A.RQST_DTTM AS `Date-Time`, "
			+ "A.RQST_USNM AS Employee, "
			+ "C.PROBLEM_DISC AS Description "
		+ "FROM SERVICE_REQUEST_PROBLEM AS C " 
			+ "LEFT JOIN SERVICE_REQUEST " 
				+ "AS A ON (C.RQST_VIN = A.RQST_VIN AND C.RQST_DTTM = A.RQST_DTTM) " 
			+ "LEFT JOIN CAR " 
				+ "AS B ON (C.RQST_VIN = B.VIN) " 
			+ "LEFT JOIN NUM_PROBS " 
				+ "AS D ON (C.RQST_VIN = D.RQST_VIN) "
		+ "ORDER BY D.NUM_PROBS DESC;";
		
		executeSQLUpdate(sqlView);
		ResultSet rs = executeSQLQuery(sqlSelect);
		
		try {
			if(rs.first()) {
				myPanel.addData(rs);
			}
			
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			
		}
	}

}
